﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Globalization;
using System.ComponentModel;

namespace VAdvantageSvc
{
    public class SmartDataSet : DataSet
    {
        //---------------------------------------------------------------------
        #region ** fields



        OracleConnection _conn = new OracleConnection();
        Dictionary<DataTable, OracleDataAdapter> _adapters = new Dictionary<DataTable, OracleDataAdapter>();

        #endregion

        //---------------------------------------------------------------------
        #region ** ctor

        public SmartDataSet(IContainer container)
            : this()
        {
            container.Add(this);
        }
        public SmartDataSet(string connectionString)
            : this()
        {
            ConnectionString = connectionString;
        }
        public SmartDataSet()
        {
            this.Locale = CultureInfo.InvariantCulture;
        }

        #endregion

        //---------------------------------------------------------------------
        #region ** object model

        /// <summary>
        /// Gets or sets the connection string used by this <see cref="SmartDataSet"/>.
        /// </summary>
        [DefaultValue("")]
        public string ConnectionString
        {
            get { return _conn.ConnectionString; }
            set { _conn.ConnectionString = value; }
        }
        /// <summary>
        /// Gets a reference to the <see cref="OracleConnection"/> used by this 
        /// <see cref="SmartDataSet"/>.
        /// </summary>
        public OracleConnection Connection
        {
            get { return _conn; }
        }
        /// <summary>
        /// Relations read from schema
        /// </summary>
        [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
        new public DataRelationCollection Relations
        {
            get { return base.Relations; }
        }
        /// <summary>
        /// Reset the <see cref="SmartDataSet"/>.
        /// </summary>
        public override void Reset()
        {
            _adapters.Clear();
            _conn.Close();
            base.Reset();
        }
        /// <summary>
        /// Fill all tables, relations, and constraints.
        /// </summary>
        public void Fill()
        {
            Fill(null);
        }
        /// <summary>
        /// Fill selected tables.
        /// </summary>
        /// <param name="tables">Array containing the names of the tables to load.</param>
        public void Fill(params string[] tables)
        {
            // clear dataset
            Reset();

            //// no conn string? no work
            //if (string.IsNullOrEmpty(ConnectionString))
            //{
            //    return;
            //}


            int i = 0;
            foreach (string table in tables)
            {
                string tbName = "Table" + i;
                string sql = table;// string.Format("select * from {0}", table.Trim());
                // OracleDataAdapter da = new OracleDataAdapter(sql, _conn);
                //IDataReader dr = VAdvantage.DataBase.DB.ExecuteReader(sql);
                DataSet ds = VAdvantage.DataBase.DB.ExecuteDataset(sql);
                //dt.Locale = CultureInfo.InvariantCulture;

                DataTable data = null;
                if (ds != null)
                {
                    data = ds.Tables[0];
                    ds.Tables.Remove(data);
                    data.TableName = tbName;
                    data = Util.SetUtcDateTime(data);
                }
                else
                {
                    data = new DataTable(tbName);
                }
                
                //Tables.Add(tbName);
                this.Tables.Add(data);
                //da.Fill(this, tbName);
                i++;
            }



            // always close the connection
            //using (_conn)
            //{
            //    // open connection
            //    _conn.Open();

            //    // get table list if not provided
            //    if (tables == null)
            //    {
            //        //tables = GetTableList();
            //    }

            //    // populate DataSet with all tables
            //    int i = 0;
            //    foreach (string table in tables)
            //    {
            //        string  tbName = "Table" + i;
            //        string sql = table;// string.Format("select * from {0}", table.Trim());
            //        OracleDataAdapter da = new OracleDataAdapter(sql, _conn);
            //        Tables.Add(tbName);
            //        da.Fill(this, tbName);
            //        i++;
            //    }

            // read relations
            // ReadRelations();

            // read constraints (unique IDs, autonumber etc)
            // ReadConstraints();
            //}
        }


        public void FillTables(params string[] tables)
        {
            // clear dataset
            Reset();

            // no conn string? no work
            if (string.IsNullOrEmpty(ConnectionString))
            {
                return;
            }

            int i = 0;
            DataSet ds;
            OracleConnection Connection = new OracleConnection(this.ConnectionString);
            try
            {
                Connection.Open();
                foreach (string table in tables)
                {
                    OracleDataAdapter adapter = new OracleDataAdapter();
                    adapter.SelectCommand = new OracleCommand(table);
                    adapter.SelectCommand.Connection = Connection;
                    ds = new DataSet();
                    string tbName = "Table" + i;
                    string sql = table;

                    adapter.Fill(ds, tbName);

                    adapter.FillSchema(ds, SchemaType.Mapped, "DataSchema" + i);

                    if (ds.Tables.Count > 1)
                    {
                        DataTable data = ds.Tables[tbName];
                        DataTable schema = ds.Tables["DataSchema" + i];
                      //  try
                      //  {
                            data.Merge(schema);
                      //  }
                      //  catch
                      //  {
                      //  }
                        ds.Tables.Remove(schema);
                        ds.Tables.Remove(data);
                        DataTable dt = new DataTable(tbName);
                        dt.Locale = CultureInfo.InvariantCulture;
                        dt = Util.SetUtcDateTime(data);
                        //Tables.Add(tbName);
                        this.Tables.Add(dt);
                        //da.Fill(this, tbName);
                    }
                    i++;
                }
            }
            finally
            {
                Connection.Close();
            }

            // always close the connection
            //using (_conn)
            //{
            //    // open connection
            //    _conn.Open();

            //    // get table list if not provided
            //    if (tables == null)
            //    {
            //        //tables = GetTableList();
            //    }

            //    // populate DataSet with all tables
            //    int i = 0;
            //    foreach (string table in tables)
            //    {
            //        string  tbName = "Table" + i;
            //        string sql = table;// string.Format("select * from {0}", table.Trim());
            //        OracleDataAdapter da = new OracleDataAdapter(sql, _conn);
            //        Tables.Add(tbName);
            //        da.Fill(this, tbName);
            //        i++;
            //    }

            // read relations
            // ReadRelations();

            // read constraints (unique IDs, autonumber etc)
            // ReadConstraints();
            //}
        }


        // <summary>
        // Saves all changes back into the database.
        // </summary>
        //public void Update()
        //{
        //    // no changes? no work
        //    if (!HasChanges())
        //    {
        //        return;
        //    }

        //    // always close the connection
        //    using (_conn)
        //    {
        //        // open connection
        //        _conn.Open();

        //        // save each table
        //        bool ok = false;
        //        int concurrencyErrors = 0;
        //        for (int errors = 0; !ok; errors++)
        //        {
        //            // assume it's all gonna work
        //            ok = true;

        //            // update all tables
        //            foreach (DataTable dt in Tables)
        //            {
        //                DataTable dtChanges = dt.GetChanges();
        //                if (dtChanges != null)
        //                {
        //                    // get data adapter
        //                    OracleDataAdapter da = GetDataAdapter(dt);

        //                    // try updating
        //                    try
        //                    {
        //                        da.Update(dtChanges);
        //                    }
        //                    catch (DBConcurrencyException cx)
        //                    {
        //                        // review: this happens occasionally and I don't know why.
        //                        // but the db does seem to update correctly, so this must be bogus...
        //                        concurrencyErrors++;
        //                        Debug.WriteLine(string.Format("concurrency exception caught: table {0}:\r\n{1}", dtChanges.TableName, cx.Message));
        //                    }
        //                    catch (Exception x)
        //                    {
        //                        // if the error is "Operation must use an updateable query.", then
        //                        // the problem must be related to permissions, maybe the database file
        //                        // is in read-only mode?
        //                        if (x.Message.IndexOf("updateable query", StringComparison.OrdinalIgnoreCase) > -1)
        //                        {
        //                            throw new InvalidProgramException("Update failed, check permissions on the database (read-only file?).");
        //                        }

        //                        // it failed, possibly because of constraint violations.
        //                        // so finish this batch, then try again a few times until all tables 
        //                        // update successfully (or until we get too many errors).
        //                        Debug.WriteLine(string.Format("exception caught: table {0}:\r\n{1}", dtChanges.TableName, x.Message));
        //                        ok = false;
        //                        if (errors > 2 * Tables.Count)
        //                        {
        //                            throw x;
        //                        }
        //                    }
        //                    Debug.WriteLine(string.Format("errors? {0}", dt.HasErrors));
        //                }
        //            }
        //        }

        //        // accept the changes (avoids most concurrency errors)
        //        AcceptChanges();
        //    }
        //}
        ///// <summary>
        ///// Delete a row, optionally deleting all its child rows.
        ///// </summary>
        ///// <param name="row">Row to delete.</param>
        ///// <param name="cascading">Whether to delete child rows.</param>
        //public void DeleteRow(DataRow row, bool cascading)
        //{
        //    // delete children
        //    if (cascading)
        //    {
        //        foreach (DataRelation relation in Relations)
        //        {
        //            if (relation.ParentTable == row.Table)
        //            {
        //                Debug.WriteLine(string.Format("Relation {0} -->> {1}", relation.ParentTable.TableName, relation.ChildTable.TableName));
        //                DataRow[] children = row.GetChildRows(relation);
        //                foreach (DataRow childRow in children)
        //                {
        //                    DeleteRow(childRow, cascading);
        //                }
        //            }
        //        }
        //    }

        //    // delete row
        //    row.Delete();
        //}
        #endregion

        //---------------------------------------------------------------------
        #region ** private

        //private string[] GetTableList()
        //{
        //    var tables = new List<string>();
        //    DataTable dt = _conn.GetSchema();//(OleDbSchemaGuid.Tables, null);
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        try
        //        {
        //            // check the table type (no views/links)
        //            string type = dr["TABLE_TYPE"].ToString().ToUpper();
        //            if (type == "TABLE")
        //            {
        //                string table = dr["TABLE_NAME"].ToString();
        //                tables.Add(table);
        //            }
        //        }
        //        catch (Exception x)
        //        {
        //            Debug.WriteLine(string.Format("Error reading table {0}: {1}", dr["TABLE_NAME"], x.Message));
        //        }
        //    }

        //    // done
        //    return tables.ToArray();
        //}
        //private void ReadConstraints()
        //{
        //    // primary key constraints
        //    var dt = _conn.GetSchema();//  .GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
        //    var uniqueTables = new Dictionary<string, string>();
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        // get primary key info
        //        string tableName = dr["TABLE_NAME"].ToString();
        //        string columnName = dr["COLUMN_NAME"].ToString();

        //        // make sure this table is in our DataSet
        //        if (!Tables.Contains(tableName))
        //        {
        //            continue;
        //        }

        //        // make sure it's unique
        //        if (uniqueTables.ContainsKey(tableName))
        //        {
        //            uniqueTables.Remove(tableName);
        //            continue;
        //        }

        //        // save and move on
        //        uniqueTables[tableName] = columnName;
        //    }

        //    // built unique list, now set up primary key columns
        //    foreach (string tableName in uniqueTables.Keys)
        //    {
        //        // set up column
        //        string columnName = (string)uniqueTables[tableName];
        //        DataTable table = Tables[tableName];
        //        DataColumn pk = table.Columns[columnName];
        //        pk.Unique = true;
        //        pk.AllowDBNull = false;

        //        // set auto increment
        //        if (pk.DataType != typeof(string) && pk.DataType != typeof(System.Guid))
        //        {
        //            pk.AutoIncrement = true;
        //            pk.ReadOnly = true;
        //            if (table.Rows.Count > 0)
        //            {
        //                string expression = string.Format("max({0})", pk.ColumnName);
        //                object max = table.Compute(expression, string.Empty);
        //                long maxID = long.Parse(max.ToString());
        //                //DataRow lastRow = table.Rows[table.Rows.Count-1];
        //                //long maxID = long.Parse(lastRow[pk].ToString());
        //                pk.AutoIncrementSeed = maxID + 1;
        //            }
        //        }

        //        // set primary key on parent table
        //        Tables[tableName].PrimaryKey = new DataColumn[] { pk };
        //    }

        //    // set default values for columns that have them,
        //    // and also for the ones that don't have defaults but are not nullable
        //    dt = _conn.GetSchema();//"COl","COlumns");// GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
        //    foreach (DataRow dr in dt.Rows)
        //    {
        //        bool isNullable = (bool)dr["IS_NULLABLE"];
        //        bool hasDefault = (bool)dr["COLUMN_HASDEFAULT"];
        //        object defVal = hasDefault ? dr["COLUMN_DEFAULT"] : DBNull.Value;

        //        if (!isNullable || defVal != DBNull.Value)
        //        {
        //            string tableName = dr["TABLE_NAME"].ToString();
        //            DataTable table = Tables[tableName];
        //            if (table != null)
        //            {
        //                string columnName = dr["COLUMN_NAME"].ToString();
        //                DataColumn column = table.Columns[columnName];
        //                if (column != null && !column.AutoIncrement && column.DataType != typeof(Guid))
        //                {
        //                    if (defVal != DBNull.Value)
        //                    {
        //                        // there is a default value, so apply it
        //                        try
        //                        {
        //                            column.DefaultValue = Convert.ChangeType(defVal, column.DataType);
        //                        }
        //                        catch
        //                        {
        //                            Debug.WriteLine(string.Format("could not apply default value for [{0}].[{1}]", tableName, columnName));
        //                        }
        //                    }
        //                    else if (!isNullable)
        //                    {
        //                        // there's no default, but the column is not nullable, so use a reasonable value
        //                        if (column.DataType == typeof(bool))
        //                        {
        //                            column.DefaultValue = false;
        //                        }
        //                        if (column.DataType == typeof(int))
        //                        {
        //                            column.DefaultValue = 0;
        //                        }
        //                        else if (column.DataType == typeof(string))
        //                        {
        //                            column.DefaultValue = string.Empty;
        //                        }
        //                    }
        //                }
        //            }
        //        }
        //    }
        //}
        //private void ReadRelations()
        //{
        //    //DataTable dt = _conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
        //    //foreach (DataRow dr in dt.Rows)
        //    //{
        //    //    // get primary/foreign table and column names
        //    //    string pkTableName = string.Format("{0}", dr["PK_TABLE_NAME"]);
        //    //    string fkTableName = string.Format("{0}", dr["FK_TABLE_NAME"]);
        //    //    string pkColumnName = string.Format("{0}", dr["PK_COLUMN_NAME"]);
        //    //    string fkColumnName = string.Format("{0}", dr["FK_COLUMN_NAME"]);

        //    //    // make sure both tables are in our DataSet
        //    //    if (!Tables.Contains(pkTableName) || !Tables.Contains(fkTableName))
        //    //        continue;

        //    //    // at design time, make sure tables are different
        //    //    // this should avoid endless loops in the MS type editor
        //    //    if (DesignMode && pkTableName == fkTableName)
        //    //        continue;

        //    //    // get unique relation name
        //    //    string relationName = pkTableName + '_' + fkTableName;
        //    //    if (Relations.Contains(relationName))
        //    //        relationName += Relations.Count.ToString();

        //    //    // add to collection
        //    //    DataColumn pkColumn = Tables[pkTableName].Columns[pkColumnName];
        //    //    DataColumn fkColumn = Tables[fkTableName].Columns[fkColumnName];
        //    //    Relations.Add(relationName, pkColumn, fkColumn, true);
        //    //}
        //}
        //private OracleDataAdapter GetDataAdapter(DataTable dt)
        //{
        //    OracleDataAdapter da;
        //    if (!_adapters.TryGetValue(dt, out da))
        //    {
        //        // create adapter as usual
        //        string text = string.Format("select * from [{0}]", dt.TableName);
        //        da = new OracleDataAdapter(text, _conn);

        //        // build commands automatically

        //        OracleCommandBuilder cb = new OracleCommandBuilder(da);
        //        //old version...
        //        //
        //        //This does work for InsertAndDeleteAndUpdate when the database is Access.
        //        //This does not work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express.
        //        //
        //        //cb.QuotePrefix = cb.QuoteSuffix = "`"; // << important!!!

        //        //new version...
        //        //
        //        //This does work for InsertAndDeleteAndUpdate when the database is Access.
        //        //This does work for InsertAndDeleteAndUpdate when the database is SqlServer2005Express.
        //        //
        //        cb.QuotePrefix = "[";
        //        cb.QuoteSuffix = "]";

        //        // fix automatic commands (ugh!)
        //        da = new OracleDataAdapter(text, _conn);
        //        if (dt.PrimaryKey.Length == 1)
        //        {
        //            DataColumn pk = dt.PrimaryKey[0];
        //            da.UpdateCommand = FixCommand(cb.GetUpdateCommand(), pk);
        //            da.InsertCommand = FixCommand(cb.GetInsertCommand(), pk);
        //            da.DeleteCommand = FixCommand(cb.GetDeleteCommand(), pk);
        //        }
        //        else
        //        {
        //            string err = string.Format("Update requires a single primary key, table '{0}' has {1} keys.", dt.TableName, dt.PrimaryKey.Length);
        //            throw new InvalidOperationException(err);
        //        }

        //        // save it for next update
        //        _adapters[dt] = da;
        //    }

        //    // return fixed adapter
        //    return da;
        //}

        //private OracleCommand FixCommand(OracleCommand cmd, DataColumn pk)
        //{
        //    // trim everything after the id in the WHERE clause
        //    // to avoid stupid concurrency violation

        //    //old... this does work with Access but not with SqlServer...
        //    //
        //    //string whereID = string.Format("WHERE ((`{0}` = ?)", pk.ColumnName);

        //    //new when using sql server, use square brackets...
        //    //
        //    string whereID = string.Format("WHERE (([{0}] = ?)", pk.ColumnName);

        //    int pos = cmd.CommandText.IndexOf(whereID, StringComparison.OrdinalIgnoreCase);
        //    if (pos > -1)
        //    {
        //        whereID = whereID.Replace("((", "(");
        //        string extraString = cmd.CommandText.Substring(pos + whereID.Length + 1);
        //        cmd.CommandText = cmd.CommandText.Substring(0, pos) + whereID;
        //        //new... remove extra parameters as well
        //        //
        //        int myCountOfQuestionMarks = this.CountStringOccurrences(extraString, "?");
        //        while (myCountOfQuestionMarks > 0)
        //        {
        //            cmd.Parameters.RemoveAt(cmd.Parameters.Count - 1);
        //            myCountOfQuestionMarks--;
        //        }
        //    }

        //    // copied from VS-generated commands (review: is this needed??)
        //    cmd.UpdatedRowSource = UpdateRowSource.Both;

        //    foreach (OracleParameter  p in cmd.Parameters)
        //    {
        //        p.Precision = 0;
        //    }

        //    // return fixed command
        //    return cmd;
        //}

        //private int CountStringOccurrences(string text, string pattern)
        //{
        //    // Loop through all instances of the string 'text'.
        //    int count = 0;
        //    int i = 0;

        //    while ((i = text.IndexOf(pattern, i)) != -1)
        //    {
        //        i += pattern.Length;
        //        count++;
        //    }

        //    return count;
        //}

        #endregion
    }
}
